﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data.OleDb;

namespace QLTH_DATASSECC.ConnectionData
{
   public class clsKetNoi
    {
       private SqlCommand _cmd;
       private SqlConnection _sqlConnect = new SqlConnection();
       private SqlDataAdapter _sqlDa;
       private DataTable _dt;
       private OleDbConnection _oledbconect = new OleDbConnection();

       protected clsKetNoi()
       {
           _sqlConnect.ConnectionString = "Server=localhost;Database=QuanLy_THPT_S3;integrated security = true";
           OpenData(false);
       }

       protected DataTable Load_DataTable(String _sql)
       {
           _sqlDa = new SqlDataAdapter(_sql, _sqlConnect);
           _dt = new DataTable();
           _sqlDa.Fill(_dt);
           return _dt;
       }

       protected SqlDataReader ExReader(String _sql)
       {
           try
           {
               _cmd = new SqlCommand(_sql, _sqlConnect);
               return _cmd.ExecuteReader();
           }
           catch
           {
               return null; 
           }
       }

       protected object CheckForDbNull(object value)
       {
           if (value == null)
           {
               return DBNull.Value;
           }

           return value;
       }
       //thực thi câu truy vấn
       protected int Nonquery(String _sql,String[] name,Object[] values,int namepara)
       {
           int _recorcd = 0;
           SqlTransaction _sqlTran = null;
           try
           {
               OpenData(false);
               _sqlTran = _sqlConnect.BeginTransaction();
               var _cmd = new SqlCommand(_sql, _sqlConnect) { Transaction = _sqlTran };
               for (int i = 0; i < namepara; i++)
               {
                   _cmd.Parameters.AddWithValue(name[i],CheckForDbNull(values[i]));
               }
               _recorcd = _cmd.ExecuteNonQuery();
               _sqlTran.Commit();
           }
           catch
           {
               if (_sqlTran != null)
                   _sqlTran.Rollback();
           }
           finally
           {
               CloseData(false);
           }
           return _recorcd;
       }

       protected object laymadk(String _sql)
       {
           try
           {
               OpenData(false);
               _cmd = new SqlCommand(_sql, _sqlConnect);
               return _cmd.ExecuteScalar();
           }
           catch
           {
               return null;
           }
           finally
           {
               CloseData(false);
           }
           
       }

       //dùng để lấy mã
       protected string laymacuoi(String _sql)
       {
           try
           {
               OpenData(false);
               _cmd = new SqlCommand(_sql, _sqlConnect);
               return (String)_cmd.ExecuteScalar();
           }
           catch
           {
               return null;
           }
           finally
           {
               CloseData(false);
           }
       }

       //open data 
       protected void OpenData(Boolean oldb)
       {
           if (!oldb)
           {
               if (_sqlConnect.State == ConnectionState.Closed)
               {
                   _sqlConnect.Open();
               }
           }
           else
           {

               if (_oledbconect.State == ConnectionState.Closed)
               {
                   _oledbconect.Open();
               }
           }
       }
       //close connect
       protected void CloseData(Boolean oldb)
       {
           if (!oldb)
           {
               if (_sqlConnect != null)
                   if (_sqlConnect.State == ConnectionState.Open)
                       _sqlConnect.Close();
           }
           else
           {
               if (_oledbconect.State == ConnectionState.Open)
               {
                   _oledbconect.Close();
               }
           }
       }

       //tạo kết nối tới file excel
      protected void ketnoi_excel(String _filename)
       {
           String[] _duoifile = _filename.Split('.');
           if (_filename != "")
           {
               if (_duoifile.Length > 1 && _duoifile[1] == "xls")
               {
                   _oledbconect.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _filename + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";

               }
               else
               {
                   _oledbconect.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + _filename + "; Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
               }
               OpenData(true);
           }
       }
       //taho tác với file excle
      protected DataTable TableExcel(String _oldb)
      {
          OleDbDataAdapter oda = new OleDbDataAdapter(_oldb, _oledbconect);
          DataTable dt = new DataTable();
          oda.Fill(dt);
          CloseData(true);
          return dt;
      }

     
    }
}
